package com.lovo.sgproj.dao.imp;

import com.lovo.sgproj.bean.ClassBean;
import com.lovo.sgproj.bean.RoomBean;
import com.lovo.sgproj.bean.StudentBean;
import com.lovo.sgproj.dao.StudentDAO;
import com.lovo.sgproj.util.DBUtil;
import org.springframework.stereotype.Component;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;

@Component
public class StudentDAOImp implements StudentDAO {
    @Override
    public ArrayList<StudentBean> findStudentByClassID(int classID) {
        ArrayList<StudentBean> stuLst = new ArrayList<>();
        String sql = "select pk_stuid,f_stuname,f_gender,f_headpic,f_studate,f_phone from t_student where fk_classid = ?";
        try (Connection con = DBUtil.getConnection();
             PreparedStatement ps = con.prepareStatement(sql)) {
            ps.setInt(1, classID);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                StudentBean stu = new StudentBean();
                stu.setStudentID(rs.getInt("pk_stuid"));
                stu.setStudentname(rs.getString("f_stuname"));
                stu.setGender(rs.getBoolean("f_gender"));
                stu.setStudentimg(rs.getString("f_headpic"));
                stu.setStudentdate(rs.getDate("f_studate"));
                stu.setStudentphone(rs.getString("f_phone"));

                stuLst.add(stu);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return stuLst;
    }

    @Override
    public ArrayList<StudentBean> findStudentByRoomID(int roomID) {
        ArrayList<StudentBean> stuLst = new ArrayList<>();
        String sql = "select pk_stuid,f_stuname,f_gender,f_headpic,f_studate,f_phone," +
                "pk_classid,f_classname," +
                "pk_roomid,f_address " +
                "from t_student,t_class,t_room where fk_classid=pk_classid and fk_roomid=pk_roomid and pk_roomid=?";
        try (Connection con = DBUtil.getConnection();
             PreparedStatement ps = con.prepareStatement(sql)) {
            ps.setInt(1, roomID);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                StudentBean stu = new StudentBean();
                stu.setStudentID(rs.getInt("pk_stuid"));
                stu.setStudentname(rs.getString("f_stuname"));
                stu.setGender(rs.getBoolean("f_gender"));
                stu.setStudentimg(rs.getString("f_headpic"));
                stu.setStudentdate(rs.getDate("f_studate"));
                stu.setStudentphone(rs.getString("f_phone"));

                ClassBean myClass = new ClassBean();
                myClass.setClassID(rs.getInt("pk_classid"));
                myClass.setClassName(rs.getString("f_classname"));

                RoomBean myRoom = new RoomBean();
                myRoom.setRoomID(rs.getInt("pk_roomid"));
                myRoom.setAddress(rs.getString("f_address"));

                stu.setTheClass(myClass);
                stu.setTheRoom(myRoom);

                stuLst.add(stu);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return stuLst;
    }

    @Override
    public ArrayList<StudentBean> findAllStudent() {
        ArrayList<StudentBean> stuLst = new ArrayList<>();
        String sql = "select pk_stuid,f_stuname,f_gender,f_headpic,f_studate,f_phone," +
                "pk_classid,f_classname," +
                "pk_roomid,f_address " +
                "from t_student,t_class,t_room where fk_classid=pk_classid and fk_roomid=pk_roomid";
        try (Connection con = DBUtil.getConnection();
             Statement st = con.createStatement()) {
            ResultSet rs = st.executeQuery(sql);
            while (rs.next()) {
                StudentBean stu = new StudentBean();
                stu.setStudentID(rs.getInt("pk_stuid"));
                stu.setStudentname(rs.getString("f_stuname"));
                stu.setGender(rs.getBoolean("f_gender"));
                stu.setStudentimg(rs.getString("f_headpic"));
                stu.setStudentdate(rs.getDate("f_studate"));
                stu.setStudentphone(rs.getString("f_phone"));

                ClassBean myClass = new ClassBean();
                myClass.setClassID(rs.getInt("pk_classid"));
                myClass.setClassName(rs.getString("f_classname"));

                RoomBean myRoom = new RoomBean();
                myRoom.setRoomID(rs.getInt("pk_roomid"));
                myRoom.setAddress(rs.getString("f_address"));

                stu.setTheClass(myClass);
                stu.setTheRoom(myRoom);

                stuLst.add(stu);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return stuLst;
    }

    @Override
    public void insertStudent(StudentBean stu) {
        String sql1 = "insert into t_student(f_stuname,f_gender,f_phone,f_studate,f_headpic,fk_classid,fk_roomid) values (?,?,?,?,?,?,?)";
        String sql2 = "select pk_roomid from t_room where f_address=?";
        String sql3 = "update t_room set f_peoplenow =f_peoplenow + 1 where pk_roomid = ?";
        try (Connection con = DBUtil.getConnection();) {
            con.setAutoCommit(false);
            try (PreparedStatement ps1 = con.prepareStatement(sql1);
                 PreparedStatement ps2 = con.prepareStatement(sql2);
                 PreparedStatement ps3 = con.prepareStatement(sql3)) {
                ps1.setString(1, stu.getStudentname());
                ps1.setBoolean(2, stu.getGender());
                ps1.setString(3, stu.getStudentphone());
                SimpleDateFormat format = new SimpleDateFormat("yyyy-MM--dd");
                java.sql.Date timePara = null;
                try {
                    timePara = new java.sql.Date(stu.getStudentdate().getTime());
                    ps1.setDate(4, timePara);

                    //毫秒数作为头像命名
                    java.util.Date Itime = new java.util.Date();
                    String str = String.valueOf(Itime.getTime());
                    try (FileInputStream fin = new FileInputStream(stu.getStudentimg());
                         FileOutputStream fos = new FileOutputStream("image/" + str + ".jpg")) {
                        byte[] b = new byte[1024];
                        int a = 0;
                        while ((a = fin.read(b)) != -1) {
                            fos.write(b, 0, a);
                        }
                    } catch (IOException e) {
                        e.printStackTrace();
                    }

                    //ps1.setString(5, stu.getStudentimg());
                    ps1.setString(5, "image/" + str + ".jpg");

                    ps1.setInt(6, stu.getTheClass().getClassID());
                    ps1.setInt(7, stu.getTheRoom().getRoomID());
                    ps1.executeUpdate();

                    ps2.setString(1, stu.getTheRoom().getAddress());
                    ResultSet rs = ps2.executeQuery();
                    while (rs.next()) {
                        int roomid = rs.getInt("pk_roomid");

                        ps3.setInt(1, roomid);
                        ps3.executeUpdate();
                        con.commit();
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                }
            } catch (SQLException e) {
                con.rollback();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public void deleteStudent(int studentID) {
        String sql1 = "update t_room set f_peoplenow = f_peoplenow - 1 where pk_roomid = (select fk_roomid from t_student where pk_stuid = ?)";
        String sql2 = "delete from t_student where pk_stuid=?";
        String sql3 = "select f_headpic from t_student where pk_stuid=?";
        try (Connection con = DBUtil.getConnection()) {
            con.setAutoCommit(false);
            try (PreparedStatement ps1 = con.prepareStatement(sql1);
                 PreparedStatement ps2 = con.prepareStatement(sql2);
                 PreparedStatement ps3 = con.prepareStatement(sql3)) {
                ps1.setInt(1, studentID);
                ps2.setInt(1, studentID);

                //删除头像
                ps3.setInt(1, studentID);
                ResultSet rs = ps3.executeQuery();
                while (rs.next()) {
                    File file = new File(rs.getString("f_headpic"));
                    file.delete();
                }

                ps1.executeUpdate();
                ps2.executeUpdate();

                con.commit();
            } catch (SQLException e) {
                con.rollback();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public StudentBean getStudentByID(int studentID) {
        StudentBean stu = new StudentBean();
        String sql = "select pk_stuid,f_stuname,f_gender,f_classname,f_phone,f_address,f_studate,pk_roomid from t_class,t_room,t_student where fk_roomid=pk_roomid and " +
                "fk_classid=pk_classid and pk_stuid=?";
        try (Connection con = DBUtil.getConnection();
             PreparedStatement ps = con.prepareStatement(sql)) {
            ps.setInt(1, studentID);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                stu.setStudentID(rs.getInt("pk_stuid"));
                stu.setStudentname(rs.getString("f_stuname"));
                stu.setGender(rs.getBoolean("f_gender"));
                ClassBean theClass = new ClassBean();
                theClass.setClassName(rs.getString("f_classname"));
                stu.setTheClass(theClass);
                stu.setStudentphone(rs.getString("f_phone"));
                RoomBean theRoom = new RoomBean();
                theRoom.setAddress(rs.getString("f_address"));
                theRoom.setRoomID(rs.getInt("pk_roomid"));
                stu.setStudentdate(rs.getDate("f_studate"));
                stu.setTheRoom(theRoom);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return stu;
    }

    @Override
    public void updateStudentRoom(int studentID, int roomID) {
        String sql1 = "update t_room set f_peoplenow = f_peoplenow - 1 where pk_roomid = (select fk_roomid from t_student where pk_stuid = ?)";
        String sql2 = "update t_room set f_peoplenow = f_peoplenow + 1 where pk_roomid = ?";
        String sql3 = "update t_student set fk_roomid = ? where pk_stuid = ?";
        try (Connection con = DBUtil.getConnection()) {
            con.setAutoCommit(false);
            try (PreparedStatement ps1 = con.prepareStatement(sql1);
                 PreparedStatement ps2 = con.prepareStatement(sql2);
                 PreparedStatement ps3 = con.prepareStatement(sql3)) {
                ps1.setInt(1, studentID);
                ps2.setInt(1, roomID);
                ps3.setInt(1, roomID);
                ps3.setInt(2, studentID);
                ps1.executeUpdate();
                ps2.executeUpdate();
                ps3.executeUpdate();
                con.commit();
            } catch (SQLException e) {
                con.rollback();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public ArrayList<StudentBean> findStudentByCondition(String stuName, String className, String roomAddress) {
        ArrayList<StudentBean> stuLst = new ArrayList<>();
        String sql = "select pk_stuid,f_stuname,f_gender,f_headpic,f_studate,f_phone," +
                "pk_classid,f_classname," +
                "pk_roomid,f_address " +
                " from t_student,t_class,t_room where fk_classid=pk_classid and fk_roomid=pk_roomid and " +
                "(f_stuname like ? and f_classname like ? and f_address like ?)";
        try (Connection con = DBUtil.getConnection();
             PreparedStatement ps = con.prepareStatement(sql)) {
            ps.setString(1, "%" + stuName + "%");
            ps.setString(2, "%" + className + "%");
            ps.setString(3, "%" + roomAddress + "%");
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                StudentBean stu = new StudentBean();

                stu.setStudentID(rs.getInt("pk_stuid"));
                stu.setStudentname(rs.getString("f_stuname"));
                stu.setGender(rs.getBoolean("f_gender"));
                stu.setStudentimg(rs.getString("f_headpic"));
                stu.setStudentdate(rs.getDate("f_studate"));
                stu.setStudentphone(rs.getString("f_phone"));

                ClassBean myClass = new ClassBean();
                myClass.setClassID(rs.getInt("pk_classid"));
                myClass.setClassName(rs.getString("f_classname"));

                RoomBean myRoom = new RoomBean();
                myRoom.setRoomID(rs.getInt("pk_roomid"));
                myRoom.setAddress(rs.getString("f_address"));

                stu.setTheClass(myClass);
                stu.setTheRoom(myRoom);

                stuLst.add(stu);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return stuLst;
    }
}
